# models.py
from flask_sqlalchemy import SQLAlchemy
from flask_test import app
import pymysql

db = SQLAlchemy()

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'your_username'
app.config['MYSQL_PASSWORD'] = 'your_password'
app.config['MYSQL_DB'] = 'student_management'



class Student(db.Model):
    __tablename__ = 'students'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    grade = db.Column(db.String(100), nullable=False)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'grade': self.grade
        }

    @classmethod
    def create_student(cls, name, grade):
        new_student = cls(name=name, grade=grade)
        db.session.add(new_student)
        db.session.commit()
        return new_student

    @classmethod
    def get_all_students(cls):
        return cls.query.all()

    @classmethod
    def get_student_by_id(cls, student_id):
        return cls.query.get(student_id)

    def update_student(self, name=None, grade=None):
        if name:
            self.name = name
        if grade:
            self.grade = grade
        db.session.commit()

    def delete_student(self):
        db.session.delete(self)
        db.session.commit()



class MysqlOperation:
    def __init__(self):
        self.username = 'root'
        self.password = '123456'
        self.host = 'localhost'
        self.port = 3306
        self.database = 'xihu'

    def connect(self):
        self.conn = pymysql.connect(
            host=self.host,
            user=self.username,
            password=self.password,
            database=self.database,
            port=self.port,
            charset='utf8'
        )
        self.cursor = self.conn.cursor()
        return self.cursor

    def query_login_userpwd(self,username):
        """
        该函数用于查询用户名中密码
        """
        pwd = ""
        try:
            self.cursor = self.connect()
            sql_yuju = 'select password from users where username="{}"'.format(username)
            self.cursor.execute(sql_yuju)
            pwd = self.cursor.fetchone()
            pwd = pwd[0]
        except Exception as e:
            print(e)
        finally:
            self.close_mysql()
        return pwd

    def query_all_students(self):
        """
        该函数用于查询所有学生数据
        """
        pwd = ""
        try:
            self.cursor = self.connect()
            sql_yuju = "select * from students"
            self.cursor.execute(sql_yuju)
            pwd = self.cursor.fetchall()
        except Exception as e:
            print(e)
        finally:
            self.close_mysql()
        return pwd


    def delete_student_by_id(self,id):
        """
        该函数用于根据id删除学生信息
        """
        res = ""
        try:
            self.cursor = self.connect()
            sql_yuju = "DELETE FROM students WHERE id = {};".format(id)
            self.cursor.execute(sql_yuju)
            # 执行删除的sql语句需要提交事务
            self.conn.commit()
            res = True
        except Exception as e:
            print(e)
            res = False
        finally:
            self.close_mysql()
        return res
    
    def get_student_by_id(self, id):
        """
        该函数用于根据id查询学生信息
        """
        res = ""
        try:
            self.cursor = self.connect()
            sql_yuju = "SELECT * FROM students WHERE id = {};".format(id)
            self.cursor.execute(sql_yuju)
            res = self.cursor.fetchone()
        except Exception as e:
            print(e)
            res = False
        finally:
            self.close_mysql()
        return res
    
    def add_student(self, *args):
        try:
            self.cursor = self.connect()
            sql_yuju = "INSERT INTO studebts VALUES ({},{});".format(args(1),args(2))
            self.cursor.execute(sql_yuju)
            res = self.cursor.fetchone()
        except Exception as e:
            print(e)
            res = False
        finally:
            self.close_mysql()
        return res

    def close_mysql(self):
        self.cursor.close()


if __name__ == "__main__":
    sql = MysqlOperation()
    password = sql.get_student_by_id(3)
    sql_yuju = f"delete from students where id={2};"
    print(password)